package com.jb.framework.database;

import com.jb.framework.ComponentCreationException;
import com.jb.framework.ComponentDeleteException;
import com.jb.framework.DataSourceManager;
import com.jb.framework.Persistable;
import com.jb.framework.PersistentDescriptor;
import com.jb.framework.PersistentManager;
import com.jb.framework.pm.EntityNotFoundException;
import com.jb.framework.util.PropUtil;
import com.jb.util.Meter;
import com.jb.web.application.configuration.Config;
import com.jb.web.model.IndexDescriptor;

import java.beans.PropertyDescriptor;

import java.math.BigDecimal;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;


public class DatabaseManager implements PersistentManager {
    protected static Log log = LogFactory.getLog(DatabaseManager.class.getName());
    HashMap descriptors = new HashMap();
    
    public DatabaseManager() {
        setSchema(Config.getInstance().getDBConfig().getSchema());
    }
    
    boolean readOnly = false;
    private String schemaName;


    public void setReadOnly(boolean readOnly) {
        this.readOnly = readOnly;
    }

    public boolean isReadOnly() {
        return readOnly;
    }

    public void setSchema(String dbName) {
        this.schemaName = dbName;
    }

    public String getSchema() {
        return schemaName;
    }
    //create root in the database
    public void create(Persistable root) {
        Connection connection = null;
        try {
            connection = DataSourceManager.getConnection(this);
            if (getId(root) == null) {
                setId(root, com.jb.web.model.GUID.next(root.getClass()));
            }
            HashMap paramList = new HashMap();
            String stmt = insertStmt(root, paramList);
            UpdateDBCommand s = (UpdateDBCommand) 
                this.cachedEntity.get(root.getClass()+"createStmt");
            if(s==null)
            {
              s =new UpdateDBCommand(stmt);
              this.cachedEntity.put(root.getClass()+"createStmt",s);
            }
            s.execute(paramList, connection);
            connection.commit();
        } catch (SQLException e) {
            if (log.isErrorEnabled()) {
                log.error(this, e);
            }

            throw new ComponentCreationException("Failed to create " + root.getId());
        } finally {
            DataSourceManager.releaseConnection(this, connection);
        }
    }
    
    public boolean createTable(Class pClass) {
        String createStmt = "create table " + tableName(pClass) + " (" + fieldsDef(pClass) +")";

        Connection connection = null;

        try {
            connection = DataSourceManager.getConnection(this);
            UpdateDBCommand.executeStmt(createStmt, connection);

            PersistentDescriptor pp = getDBDescriptor(pClass);
            Iterator constraints = pp.getIndexes().iterator();
            int j = 0;

            while (constraints.hasNext()) {
                IndexDescriptor id = (IndexDescriptor) constraints.next();
                String[] columns = id.getFields();

                if (columns.length == 0) {
                    continue;
                }

                String createConstraint = "create " + id.getType() + " index " + tableName(pClass) + (j++) +
                    "_idx on " + tableName(pClass) + " (";

                for (int i = 0; i < columns.length; i++) {
                    createConstraint += columns[i];

                    if (i < (columns.length - 1)) {
                        createConstraint += ",";
                    }
                }

                createConstraint += ");";
                com.jb.framework.database.UpdateDBCommand.executeStmt(createConstraint, connection);
            }
            connection.commit();
            return true;
        } catch (SQLException e) {
            if(log.isErrorEnabled())
                log.error("Failed on "+createStmt,e);
            return false;
        } finally {
            DataSourceManager.releaseConnection(this, connection);
        }
    }

    public void delete(Class childClass, Predicate predicate) {
        Connection connection = null;

        try {
            connection = DataSourceManager.getConnection(this);

            String stm = "delete from " + tableName(childClass) +
                ((predicate == null) ? "" : (" where " + predicate.toString()));

            com.jb.framework.database.UpdateDBCommand.executeStmt(stm, predicate, connection);
        } catch (Exception e) {
            if (log.isErrorEnabled()) {
                log.error(this, e);
            }
        } finally {
            DataSourceManager.releaseConnection(this, connection);
        }
    }

    /**
     * Delete a persistable from database.
     *
     * @param root DOCUMENT ME!
     */
    public void delete(Persistable root) {
        UpdateDBCommand s = new UpdateDBCommand(deleteStmt(root));

        Connection connection = null;

        try {
            connection = DataSourceManager.getConnection(this);
            s.execute(connection);
        } catch (SQLException e) {
            if (log.isErrorEnabled()) {
                log.error(this, e);
            }

            throw new ComponentDeleteException("Failed to delete " + root.getId());
        } finally {
            DataSourceManager.releaseConnection(this, connection);
        }
    }

    /**
     * Drops a database
     *
     * @param dbName base class of the table to be dropped
     *
     * @throws SQLException on database errors.
     */
    public void dropSchema(String dbName) throws SQLException {
        String dropStmt = "drop database " + dbName;
        Connection connection = DataSourceManager.getConnection(this);

        try {
            UpdateDBCommand.executeStmt(dropStmt, connection);
        } finally {
            DataSourceManager.releaseConnection(this, connection);
        }
    }

    /**
     * Drops a table
     *
     * @param pClass base class of the table to be dropped
     *
     * @throws SQLException on database errors.
     */
    public boolean dropTable(Class pClass) {
        String dropStmt = "drop table " + tableName(pClass);
        Connection connection = null;

        try {
            connection = DataSourceManager.getConnection(this);
            UpdateDBCommand.executeStmt(dropStmt, connection);
            connection.commit();
            return true;
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException _e) {

            }
            if(log.isErrorEnabled())
                log.error("Failed to dropTable ",e);
            return false;
        } finally {
            DataSourceManager.releaseConnection(this, connection);
        }
    }

    public boolean exists(Class clazz) {
        String testStmt = "select id from " + tableName(clazz);
        Connection connection = null;

        try {
            connection = DataSourceManager.getConnection(this);
            UpdateDBCommand.executeStmt(testStmt, connection);
            connection.commit();
            return true;
        } catch (SQLException e) {
            if(log.isErrorEnabled())
                log.error("Failed to test exist ",e);
            try {
                connection.rollback();
            } catch (SQLException _e) {

            }
            return false;
        } finally {
            DataSourceManager.releaseConnection(this, connection);
        }
    }

    /**
     * Finds a list of objects for given type and predicate.
     *
     * @param childClass Type of the objects
     * @param predicate predicate for the query
     * @param limit max number
     *
     * @return List of objects, If no found, empty list is returned
     */
    public List find(Class childClass, Predicate predicate, long limit) {
        if (log.isDebugEnabled()) {
            Meter.start();
        }

        ArrayList children = new ArrayList();
        Connection connection = null;

        try {
            connection = DataSourceManager.getConnection(this);

            String stm = selectStmt(childClass) + ((predicate == null) ? "" : (" where " + predicate.toString()));

            SelectDBCommand sdbc = new SelectDBCommand(stm, predicate) {
                    public Object populate(Object visitor)
                        throws SQLException {
                        try {
                            Object obj = ((Class) visitor).newInstance();
                            ResultSet rs = getResultSet();
                            com.jb.framework.util.PropUtil.updateWithResultSet(obj, rs);

                            if (obj instanceof Persistable) {
                                ((Persistable) obj).postCreate(null);
                            }

                            return obj;
                        } catch (Exception e) {
                            if (log.isErrorEnabled()) {
                                log.fatal(this, e);
                            }
                        }

                        return null;
                    }
                };

            try {
                sdbc.execute(connection);

                int i = 1;

                while (sdbc.next()) {
                    Object child = sdbc.populate(childClass);

                    if (child != null) {
                        children.add(child);
                    }

                    if (i >= limit) {
                        break;
                    }

                    i++;
                }
            } finally {
                sdbc.destroy();
            }
        } catch (Exception e) {
            if (log.isErrorEnabled()) {
                log.error(this, e);
            }
        } finally {
            if (log.isDebugEnabled()) {
                Meter.stop("find " + limit + " of " + childClass.getName());
            }

            DataSourceManager.releaseConnection(this, connection);
        }

        return children;
    }

    /**
     * Finds a list of object instances for a given class, subject to the predicate.
     *
     * @param childClass class of which object instances are found.
     * @param predicate predicate for this query.
     *
     * @return a list of objects. If no found, empty list is returned
     */
    public List find(Class childClass, String stmt, Predicate predicate) {
        if (log.isDebugEnabled()) {
            Meter.start();
        }

        ArrayList children = new ArrayList();

        Connection connection = null;

        try {
            connection = DataSourceManager.getConnection(this);

            String stm = stmt + ((predicate == null) ? "" : (" where " + predicate.toString()));

            if (log.isDebugEnabled()) {
                log.debug("Execute find statement for " + childClass + " " + stmt);
            }

            SelectDBCommand sdbc = new SelectDBCommand(stm, predicate) {
                    public Object populate(Object visitor)
                        throws SQLException {
                        try {
                            Object obj = ((Class) visitor).newInstance();
                            ResultSet rs = getResultSet();
                            com.jb.framework.util.PropUtil.updateWithResultSet(obj, rs);

                            if (obj instanceof Persistable) {
                                ((Persistable) obj).postCreate(null);
                            }

                            return obj;
                        } catch (Exception e) {
                            if (log.isErrorEnabled()) {
                                log.error(this, e);
                            }
                        }

                        return null;
                    }
                };

            try {
                sdbc.execute(connection);

                while (sdbc.next()) {
                    Object child = sdbc.populate(childClass);

                    if (child != null) {
                        children.add(child);
                    }
                }
            } finally {
                sdbc.destroy();
            }
            connection.commit();
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (SQLException _e) {

            }
            if (log.isErrorEnabled()) {
                log.error(this, e);
            }
        } finally {
            DataSourceManager.releaseConnection(this, connection);

            if (log.isDebugEnabled()) {
                Meter.stop("run " + stmt);
            }
        }

        return children;
    }

    /**
     * Finds a list of object instances for a given class, subject to the predicate.
     *
     * @param childClass class of which object instances are found.
     * @param predicate predicate for this query.
     *
     * @return a list of objects. If no found, empty list is returned
     */
    public List find(Class childClass, Predicate predicate) {
        if (log.isDebugEnabled()) {
            Meter.start();
        }

        ArrayList children = new ArrayList();

        Connection connection = null;

        try {
            connection = DataSourceManager.getConnection(this);

            String stm = selectStmt(childClass) + ((predicate == null) ? "" : (" where " + predicate.toString()));

            SelectDBCommand sdbc = new SelectDBCommand(stm, predicate) {
                    public Object populate(Object visitor)
                        throws SQLException {
                        try {
                            Object obj = ((Class) visitor).newInstance();
                            ResultSet rs = getResultSet();
                            PropUtil.updateWithResultSet(obj, rs);

                            if (obj instanceof Persistable) {
                                ((Persistable) obj).postCreate(null);
                            }

                            return obj;
                        } catch (Exception e) {
                            if (log.isErrorEnabled()) {
                                log.error(this, e);
                            }
                        }

                        return null;
                    }
                };

            try {
                sdbc.execute(connection);

                while (sdbc.next()) {
                    Object child = sdbc.populate(childClass);

                    if (child != null) {
                        children.add(child);
                    }
                }
            } finally {
                sdbc.destroy();
            }
            connection.commit();
        } catch (SQLException e) {
            if (log.isErrorEnabled()) {
                log.error(this, e);
            }
            try {
                if(connection!=null)
                    connection.rollback();
            } catch (SQLException _e) {

            }
        } finally {
            if (log.isDebugEnabled()) {
                Meter.stop("find " + childClass.getName());
            }

            DataSourceManager.releaseConnection(this, connection);
        }

        return children;
    }

    /**
     * Finds the predicated bean
     *
     * @param root
     * @param predicate
     *
     * @return
     *
     * @throws IllegalArgumentException DOCUMENT ME!
     */
    public boolean find(Object root, Predicate predicate) {
        if ((root == null) || (predicate == null) || (predicate.toString().length() == 0)) {
            throw new IllegalArgumentException("Object and predicate must not be null");
        }

        int i = 0;
        String stmt = selectStmt(root.getClass()) + " where " + predicate.toString();

        if (log.isDebugEnabled()) {
            log.debug("find " + stmt);
        }

        SelectDBCommand sdbc = new SelectDBCommand(stmt, predicate);

        Connection connection = null;

        try {
            connection = DataSourceManager.getConnection(this);
            sdbc.execute(connection);

            while (sdbc.next()) {
                // sdbc.populate(root);
                i++;
            }
            connection.commit();
        } catch (SQLException e) {
            log.debug("find " + stmt, e);
            try {
                connection.rollback();
            } catch (SQLException _e) {

            }
            return false;
        } finally {
            if (sdbc != null) {
                try {
                    sdbc.destroy();
                } catch (Exception e) {
                    log.debug("find " + stmt, e);
                }
            }

            DataSourceManager.releaseConnection(this, connection);
        }

        return i > 0;
    }

    /**
     * Locates an object instance from database
     *
     * @param clazz type of object
     * @param id object id
     *
     * @return object found.
     *
     */
    public Object findById(Class clazz, Object id) {
        Predicate idPredicate = new Predicate("id", id, "=");
        List list = find(clazz, idPredicate);

        if (list.size() == 1) {
            return list.get(0);
        }

        if (list.size() == 0) {
            return null; //not found  
        } else {
            throw new DBException("cardinal error : " + idPredicate + " " + list.size());
        }
    }

    /**
     * Loads a persistable into memory
     *
     * @param root DOCUMENT ME!
     *
     * @return DOCUMENT ME!
     */
    public boolean load(Persistable root) {
        return find(root, true);
    }

    /**
     * Registers a descripor for a given class.
     *
     * @param clazz DOCUMENT ME!
     * @param persistentDescriptor DOCUMENT ME!
     */
    public void registerDescriptor(Class clazz, PersistentDescriptor persistentDescriptor) {
        descriptors.put(clazz, persistentDescriptor);
    }

    /**
     * DOCUMENT ME!
     *
     * @param bean DOCUMENT ME!
     * @param saveChildren DOCUMENT ME!
     */
    public void save(Persistable bean, boolean saveChildren) {
        save(bean);
    }

    /**
     * DOCUMENT ME!
     *
     * @param root DOCUMENT ME!
     */
    public void save(Persistable root) {
        if (!find(root, false)) {
            create(root);
            return;
        }

        HashMap bindingVariables = new HashMap();
        String updateStatement = updateStmt(root, bindingVariables);
        UpdateDBCommand s = new UpdateDBCommand(updateStatement);
        Connection connection = null;

        try {
            connection = DataSourceManager.getConnection(this);
            s.execute(bindingVariables, connection);
            connection.commit();
        } catch (SQLException e) {
            if (log.isErrorEnabled()) {
                log.error(this, e);
            }
            try {
                connection.rollback();
            } catch (SQLException _e) {

            }
        } finally {
            DataSourceManager.releaseConnection(this, connection);
        }
    }

    private String getColumnTypeDef(Class pClass, Object prop, String pName) {
        if (prop instanceof PropertyDescriptor) {
            PropertyDescriptor pd = (PropertyDescriptor) prop;
            Class propType = pd.getPropertyType();
            String propTypeName = "" + propType;
            PersistentDescriptor pp = getDBDescriptor(pClass);

            String type = pp.getFieldType(propTypeName, pName);

            if (pp.isPrimaryKey(pName)) {
                type += " primary key";
            }

            return type;
        } else {
            return "VARCHAR(64)";
        }
    }

    private PersistentDescriptor getDBDescriptor(Class pClass) {
        return (PersistentDescriptor) descriptors.get(pClass);
    }

    private void setId(Object obj, String objId) {
        try {
            PropertyUtils.setProperty(obj, "id", objId);
        } catch (Exception e) {
            if (log.isErrorEnabled()) {
                log.error(this, e);
            }
        }
    }

    private String getId(Object obj) {
        try {
            String idStr = (String) PropertyUtils.getProperty(obj, "id");

            if (idStr == null) {
                return null;
            }

            idStr = idStr.replaceAll("'", "''");

            return idStr;
        } catch (Exception e) {
            if (log.isErrorEnabled()) {
                log.error(this, e);
            }
        }

        return null;
    }

    private Object getPropertyValue(Object root, Object prop) {
        if (prop instanceof PropertyDescriptor) {
            PropertyDescriptor pd = (PropertyDescriptor) prop;
            Object value = null;

            try {
                value = PropertyUtils.getProperty(root, pd.getName());
            } catch (Exception e) {
                if (log.isErrorEnabled()) {
                    log.error(this, e);
                }

                return null;
            }

            Class propType = pd.getPropertyType();

            if (propType == boolean.class) {
                return new Boolean(value.toString());
            }

            if (propType == int.class) {
                return new BigDecimal(value.toString());
            }

            if (propType == long.class) {
                return new BigDecimal(value.toString());
            } else if (propType == java.util.Date.class) {
                return value;
            } else if (propType == java.util.Calendar.class) {
                return value;
            } else if (value instanceof Persistable) {
                return ((Persistable) value).getId();
            } else {
                return (value == null) ? "" : value.toString();
            }
        } else {
            return "";
        }
    }

    private String deleteStmt(Object root) {
        String stmt = "delete from " + tableName(root.getClass()) + " where id = '" + getId(root) + "'";

        return stmt;
    }

    private String fieldsDef(Class pClass) {
        StringBuffer createStmt = new StringBuffer();
        HashMap hm = com.jb.framework.util.PropUtil.getPropertyDescriptorsMap(pClass);
        Iterator it = hm.keySet().iterator();

        while (it.hasNext()) {
            String pName = (String) it.next();
            createStmt.append(pName + " " + getColumnTypeDef(pClass, hm.get(pName), pName));

            if (it.hasNext()) {
                createStmt.append(",\n");
            }
        }

        return createStmt.toString();
    }

    private String fieldsList(Class pClass) {
        String createStmt = "";
        Iterator it = com.jb.framework.util.PropUtil.getPropertyNames(pClass).iterator();

        while (it.hasNext()) {
            String pName = (String) it.next();
            createStmt = createStmt + pName;

            if (it.hasNext()) {
                createStmt = createStmt + ",\n";
            }
        }

        return createStmt;
    }

    //Find means it exist
    //if populate is true, values from database will be used to update root
    //return true if only one found in the database
    private boolean find(Object root, boolean populate)
        throws EntityNotFoundException {
        if (root == null) {
            throw new IllegalArgumentException("Object must not be null");
        }

        if (getId(root) == null) {
            return false;
        }

        String id = getId(root).replaceAll("'", "''");
        String stmt = selectStmt(root.getClass()) + " where id='" + id + "'";

        if (log.isDebugEnabled()) {
            log.debug("find " + stmt);
        }

        SelectDBCommand sdbc = new SelectDBCommand(stmt, null) {
                public Object populate(Object visitor)
                    throws SQLException {
                    ResultSet rs = getResultSet();
                    com.jb.framework.util.PropUtil.updateWithResultSet(visitor, rs);

                    return this;
                }
            };

        Connection connection = null;

        try {
            connection = DataSourceManager.getConnection(this);
            sdbc.execute(connection);
            ResultSet rs = sdbc.getResultSet();
            boolean found = rs.next();

            if (found && populate) {
                sdbc.populate(root);
            }
            connection.commit();
            return found; //found
        } catch (SQLException e) {
            if (log.isFatalEnabled()) {
                log.error("find " + stmt, e);
            }
            try {
                connection.rollback();
            } catch (SQLException _e) {

            }

            throw new EntityNotFoundException(e.getMessage());
        } finally {
            if (sdbc != null) {
                try {
                    sdbc.destroy();
                } catch (Exception e) {
                    if (log.isFatalEnabled()) {
                        log.error("find " + stmt, e);
                    }
                }
            }
            DataSourceManager.releaseConnection(this, connection);
        }
    }
    
    static HashMap cachedEntity=new HashMap();
    private String insertStmt(Object root, HashMap paramList) {
        String className = root.getClass().getName();

        String insertStmt = (String) cachedEntity.get(className+"Insert");
        HashMap hm = (HashMap) cachedEntity.get(className+"Properties");
        if(hm==null)
        {
          hm = com.jb.framework.util.PropUtil.getPropertyDescriptorsMap(root.getClass());
          cachedEntity.put(className+"Properties", hm);
          System.err.println("Cache property map");
        } 
        boolean cached=insertStmt!=null;
        String colNameList = "(";
        String colValueList = "(";
        if(!cached)
        {
          insertStmt = "insert into " + tableName(root.getClass());
        }

        Iterator it = hm.keySet().iterator();
        int i = 1;

        while (it.hasNext()) {
            String pName = (String) it.next();
            String pValue = hm.get(pName).toString();

            if (pValue.length() != 0) {
                try {
                    if(!cached)            
                      colValueList = colValueList + "?";
                    paramList.put("" + i, getPropertyValue(root, hm.get(pName)));
                    i++;
                   if(!cached) 
                    colNameList = colNameList + pName;
                } catch (Exception e) {
                    if (log.isErrorEnabled()) {
                        log.error(this, e);
                    }
                }
            }

            if (it.hasNext() && !cached) {
                colValueList = colValueList + ",\n";
                colNameList = colNameList + ",\n";
            }
        }
        if(!cached)
        {
          colValueList = colValueList + ")";
          colNameList = colNameList + ")";
          insertStmt = insertStmt + " " + colNameList + " values " + colValueList + "\n";
          cachedEntity.put(className+"Insert", insertStmt);
        }
        
        return insertStmt;
    }

    private String selectStmt(Class pClass) {
        String selectStmt = "select ";
        selectStmt = selectStmt + fieldsList(pClass);
        selectStmt = selectStmt + " FROM " + tableName(pClass);

        return selectStmt;
    }

    private String tableName(Class pClass) {
        String className = pClass.getName();
        int lastLoc = className.lastIndexOf('.');
        className = "g_" + className.substring(lastLoc + 1);

        return className.toLowerCase();
    }

    private String updateStmt(Object root, HashMap bindingVariable) {
        bindingVariable.clear();

        String updateStmt = "update " + tableName(root.getClass()) + " set ";
        HashMap hm = com.jb.framework.util.PropUtil.getPropertyDescriptorsMap(root.getClass());
        Iterator it = hm.keySet().iterator();
        int i = 1;

        while (it.hasNext()) {
            String pName = (String) it.next();

            Object value = getPropertyValue(root, hm.get(pName));
            updateStmt = updateStmt + pName + " = ?";
            bindingVariable.put("" + i, value);
            i++;

            if (it.hasNext()) {
                updateStmt = updateStmt + ",\n";
            }
        }

        updateStmt = updateStmt + " where id = '" + getId(root) + "'";

        return updateStmt;
    }
}
